﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using LinQ_DTO;

namespace DAO
{
    public class SanPhamDAO
    {

        public List<shop_sanpham> LayTatCaSanPham()
        {
            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            var products = from c in EFashionDB.shop_sanphams select c;
            return products.ToList();
        }

        public List<shop_sanpham> LaySanPhamTheoLoai(int MaLoaiSP)
        {
            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            var products = from c in EFashionDB.shop_sanphams
                           where c.MaLoaiSanPham == MaLoaiSP
                           select c;
            return products.ToList();
        }

        public List<shop_sanpham> LaySanPhamTheoMa(int MaSP)
        {
            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            var products = from c in EFashionDB.shop_sanphams
                           where c.MaSanPham == MaSP
                           select c;
            return products.ToList();
        }

        public void ThemSanPham(shop_sanpham a)   
        {
            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            EFashionDB.shop_sanphams.InsertOnSubmit(a);
            EFashionDB.SubmitChanges();

        }

        public void XoaSanPhamTheoMa(int MaSP)
        {

            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            var List = from a in EFashionDB.shop_sanphams
                          where a.MaSanPham == MaSP
                          select a;
            shop_sanpham ResultCmt = List.Single();
            EFashionDB.shop_sanphams.DeleteOnSubmit(ResultCmt);
            EFashionDB.SubmitChanges();

        }

        public void CapNhatSP(shop_sanpham Sp)
        {

            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            shop_sanpham select = EFashionDB.shop_sanphams.Single(c => c.MaSanPham== Sp.MaSanPham);
            if (select != null)
            {

                select.DonGia = Sp.DonGia;
                select.HinhSanPham = Sp.HinhSanPham;
                select.KhuyenMai = Sp.KhuyenMai;
                select.MaLoaiSanPham = Sp.MaLoaiSanPham;
                select.MaNhaSanXuat = Sp.MaNhaSanXuat;
                select.MaSanPham = Sp.MaSanPham;
                select.MoTa = Sp.MoTa;
                select.NgayNhap = Sp.NgayNhap;
                select.Phai = Sp.Phai;
                select.Size = Sp.Size;
                select.SoLuongCon = Sp.SoLuongCon;
                select.SoLuongNhap = Sp.SoLuongNhap;
                select.TenSanPham = Sp.TenSanPham;
            }
            EFashionDB.SubmitChanges();

        }



        ///////////////Code by Nmcuong////////////////////////

        public List<shop_sanpham> GetAllProduct()
        {
            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            var products = from c in EFashionDB.shop_sanphams select c;
            return products.ToList();
        }

       

        public List<shop_sanpham> LaySanPhamHotTheoPhai(bool Phai)
        {
            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            var products = from c in EFashionDB.shop_sanphams
                           where c.Phai == Phai
                           orderby c.shop_chamdiems.Sum(o => o.Diem) descending
                           select c;
           // products.OrderByDescending(p => LayDiemChoSanPham(p));
            return products.ToList();
            
        }

        public List<shop_sanpham> LaySanPhamHot()
        {
            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            var products = from c in EFashionDB.shop_sanphams
                           orderby c.shop_chamdiems.Sum(o => o.Diem) descending
                           select c;
            return products.ToList();

        }

        public List<shop_sanpham> LaySanPhamHotTheoLoaiPhai(bool Phai, int MaLoai)
        {
            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            var products = from c in EFashionDB.shop_sanphams
                           where c.Phai == Phai && c.MaLoaiSanPham == MaLoai
                          // orderby c.Diem
                           select c;
            return products.ToList();

        }

        public List<shop_sanpham> LaySanPhamKhuyenMaiTheoPhai(bool Phai)
        {
            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            var products = from c in EFashionDB.shop_sanphams
                           from sp_km in EFashionDB.shop_sanpham_khuyenmais
                           where c.Phai == Phai && c.MaSanPham == sp_km.MaSanPham
                          // orderby c.Diem
                           select c;
            return products.ToList();
        }

        public List<shop_sanpham> LaySanPhamKhuyenMaiTheoLoaiPhai(bool Phai, int MaLoai)
        {
            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            var products = from c in EFashionDB.shop_sanphams
                           from sp_km in EFashionDB.shop_sanpham_khuyenmais
                           where c.Phai == Phai && c.MaSanPham == sp_km.MaSanPham && c.MaLoaiSanPham == MaLoai
                           // orderby c.Diem
                           select c;
            return products.ToList();
        }

        public List<shop_sanpham> LaySanPhamKhuyenMai()
        {
            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            var products = from c in EFashionDB.shop_sanphams
                           from sp_km in EFashionDB.shop_sanpham_khuyenmais
                           where c.MaSanPham == sp_km.MaSanPham
                           // orderby c.Diem
                           select c;
            return products.ToList();
        }

       

        public List<shop_sanpham> LaySanPhamTheoPhai(bool Phai)
        {
            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            var products = from c in EFashionDB.shop_sanphams
                           where c.Phai == Phai
                           select c;
            return products.ToList();
        }

        public List<shop_sanpham> LaySanPhamTheoLoaiPhai(bool Phai, int MaLoai)
        {
            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            var products = from c in EFashionDB.shop_sanphams
                           where c.Phai == Phai && c.MaLoaiSanPham == MaLoai
                           select c;
            return products.ToList();
        }

       

        public void CapNhatDiem(shop_sanpham Sp)
        {
            //EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            //shop_sanpham product = EFashionDB.shop_sanphams.Single(p => p.MaSanPham == Sp.MaSanPham);
            //product.Diem = Sp.Diem;
            //product.SoNguoiChoDiem = Sp.SoNguoiChoDiem;
            //EFashionDB.SubmitChanges();
        }

        public void ThemDiemChoSanPham(shop_chamdiem NewRecord)
        {
            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            EFashionDB.shop_chamdiems.InsertOnSubmit(NewRecord);
            
            try
            {
                EFashionDB.SubmitChanges();
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.Message);
            }

        }

        public int LayDiemChoSanPham(shop_sanpham Sp)
        {
            int Diem = 0;
            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();

            var ListDiem = from DiemRecord in EFashionDB.shop_chamdiems
                           where DiemRecord.MaSanPham == Sp.MaSanPham
                           select DiemRecord;

            foreach (shop_chamdiem Record in ListDiem)
                Diem += (int)Record.Diem;
            return Diem;

        }

        int SortSPByDiem(shop_sanpham Sp)
        {
            int Diem = 0;
            Diem = LayDiemChoSanPham(Sp);
            //return Diem;
            return Sp.MaSanPham;
        }

        public List<shop_sanpham> TimSanPhamTheoTen(string TenSp)
        {
            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            var products = from c in EFashionDB.shop_sanphams
                           where c.TenSanPham.Contains(TenSp)
                           select c;
            return products.ToList();
        }

        public List<shop_sanpham> TimSanPhamTheoLoaiGiaNhaSX(decimal LowestCost, decimal HighestCost, int MaLoai, int MaNSX)
        {
            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            if (MaLoai == 0)
            {
                if (MaNSX == 0)
                {
                    var products = from c in EFashionDB.shop_sanphams
                                   where (c.DonGia >= LowestCost) && (c.DonGia <= HighestCost)
                                   select c;
                    return products.ToList();
                }
                else
                {
                    var products = from c in EFashionDB.shop_sanphams
                                   where (c.DonGia >= LowestCost) && (c.DonGia <= HighestCost) && (c.MaNhaSanXuat == MaNSX)
                                   select c;
                    return products.ToList();
                }
            }
            else
            {
                if (MaNSX == 0)
                {
                    var products = from c in EFashionDB.shop_sanphams
                                   where (c.DonGia >= LowestCost) && (c.DonGia <= HighestCost) && (c.MaLoaiSanPham == MaLoai)
                                   select c;
                    return products.ToList();
                }
                else
                {
                    var products = from c in EFashionDB.shop_sanphams
                                   where (c.DonGia >= LowestCost) && (c.DonGia <= HighestCost) && (c.MaLoaiSanPham == MaLoai) && (c.MaNhaSanXuat == MaNSX)
                                   select c;
                    return products.ToList();
                }
            }

        }

        //////////////////end Nmcuong///////////////////

        /************************************************************************************************************        *                          coder: TRAN KINH LY 
         ***********************************************************************************************************/
        public List<shop_sanpham> LaySanPhamGioiHan(int iStart, int iLimit)
        {
            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            var sp = (from c in EFashionDB.shop_sanphams
                      select c).Skip(iStart).Take(iLimit);
            return sp.ToList();
        }

        public int LaySoLuongSanPham()
        {
            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            int totalrow = EFashionDB.shop_sanphams.Count();
            return totalrow;
        }

        public List<shop_sanpham> LaySanPhamTheoKhoa(String key_search)
        {
            EFashionDBDataContext EFashionDB = new EFashionDBDataContext();
            var sp = from c in EFashionDB.shop_sanphams
                     where c.TenSanPham.Contains(key_search)
                     select c;
            return sp.ToList();
        }
        /************************************************************************************************************        *                          coder: TRAN KINH LY 
         ***********************************************************************************************************/

    }
}
